<?php
include_once("../Dao/db.php");
Class DupKey
{
	var $media=-1;
	
	var $mediaList="<option value='-1' >(指定なし)";
	
	var $camp=-1;
	
	var $campList="<option value='-1' >(指定なし)";
	
	var $keyId;
	
	var $keyName="";
	
	var $show_deleted=0;
	
	function MyTask()
	{
		session_start();
		$this->iniForm();
		$this->getList();

	}
	
	function iniForm()
	{
		if(isset($_POST["search_media_select"])) $this->media = $_POST["search_media_select"];
		if(isset($_POST["search_campaign_select"])) $this->camp = $_POST["search_campaign_select"];
		if(isset($_POST["search_text2"])) $this->keyName = $_POST["search_text2"];
		
		if(isset($_POST["show_deleted"])) $this->show_deleted = $_POST["show_deleted"];
		
	}	
	
	function getList()
	{
		$this->getMeida();
		$this->getCamp();
		
	}
	
	function getMeida()
	{
		$sql = "select id,name from listingmst";
		//$sql .="where memberId = ".$_SESSION["memberId"]." ";
		$result = mysql_query($sql);
		$rows = mysql_num_rows($result);
		if ($rows > 0)
		{
			for ($i=0;$i<$rows;$i++)
			{
				$data = mysql_fetch_row($result);				
				$this->mediaList = $this->mediaList."<option id=".$i." value=".$data[0].">".$data[1]."</option>";
			}			
		}
	}
	
	function getCamp()
	{
		$sql = "select camp_Id,name from campaign ";
		$sql .="where memberId = ".$_SESSION["memberId"]." ";
		$sql .=" and status<>9 ";
		//echo $sql;
		$result = mysql_query($sql);
		$rows = mysql_num_rows($result);
		if ($rows > 0)
		{
			for ($i=0;$i<$rows;$i++)
			{
				$data = mysql_fetch_row($result);				
				$this->campList = $this->campList."<option id=".$i." value=".$data[0].">".$data[1]."</option>";
			}			
		}
	}
	
	function getSelectSql()
	{
		$html = "";
		$list = "'-1'";
		
		$sql = " select K.text as keyName,count(key_Id) as count ";
		$sql.= " from campaign as C,adgroup A,keyword as K ";
		$sql.= " where C.memberId = ".$_SESSION["memberId"]." ";
		
		if($this->camp != -1)
		 	//$sql.= " and C.camp_Id =".$this->camp;
		if($this->media != -1)
			$sql.= " and C.listingType =".$this->media;
		
		$sql.= " and A.camp_Id=C.camp_Id ";
		$sql.= " and K.adGr_Id=A.adGr_Id ";
		$sql.= " and K.negative<>1 ";
		//20090205 cai ADD START
        $sql.= " and K.status!=9 ";
		$sql.= " and C.status!=9 ";
		$sql.= " and A.status!=9 ";
        //20090205 cai ADD END
		$sql.= " group by K.text ";
		$sql.= " having count>1";
		
		echo '<!--'.$sql.'-->';
		$result = mysql_query($sql);
		$rows = mysql_num_rows($result);
		if ($rows > 0)
		{
			for ($i=0;$i<$rows;$i++)
			{
				$data = mysql_fetch_row($result);	
				$list .= ",'".$data[0]."'";			
				
			}
			
			$sql = " select campaign.Name, adgroup.Name, keyword.text ";
			$sql .= " FROM campaign, adgroup, keyword ";
			$sql .= " where campaign.memberId = ".$_SESSION["memberId"]." ";
			$sql .= " AND campaign.camp_Id = adgroup.camp_Id ";
			$sql .= " AND keyword.adGr_Id = adgroup.adGr_Id ";
			$sql.= " and keyword.status!=9 ";
			$sql.= " and campaign.status!=9 ";
			$sql.= " and adgroup.status!=9 ";
			$sql .= " AND keyword.text in (".$list.")";
			if($this->camp != -1) $sql.= " and campaign.camp_Id =".$this->camp;
		    if($this->media != -1) $sql.= " and campaign.listingType =".$this->media;
			if($this->keyName != "") $sql.= " and keyword.text like '%".$this->keyName."%'";
			echo '<!--'.$sql.'-->';
			$result2 = mysql_query($sql);
			$rows = mysql_num_rows($result2);
			
			if ($rows > 0){
				$html.=	'<table class="tbl" width="100%" border="0" cellpadding="4" cellspacing="0">';
				$html.=	'<tr class="tr2" align="center"><td align="center">No</td><td align="left">キャンペーン</td>';
				$html.=	'<td align="left">広告グループ</td><td align="left">キーワード</td></tr>';
				for ($i=0;$i<$rows;$i++)
				{
					$data = mysql_fetch_row($result2);	
					if($i%2 == "0"){
						$html .= ' <tr class="tr4" align="center">';
					}else{
						$html .= ' <tr class="tr3" align="center">';
					}
					$html .= '<td align="center">'.($i+1).'</td>';
					$html .= '<td align="left">'.$data[0].'</td>';
					$html .= '<td align="left">'.$data[1].'</td>';
					$html .= '<td align="left">'.$data[2].'</td></tr>';
				}
				$html .= '</table>';
				$html .= '<table width="100%" border="0" cellpadding="2" cellspacing="0">';
  				$html .= '<tr><td align="left"></td><td align="right"> 1件-'.$rows.'件/ '.$rows.'件&nbsp;&nbsp; </td></tr></table>';
			} else {
				$html .= "データはありません。";
			}
			 			
		} else {
			$html .= "データはありません。";
		}
			
		echo $html;
	}
	
	function getSelectSqlForExclude()
	{
		$html = "";
		
		$sql = " select campaign.Name, adgroup.Name, keyword.text,keyword.sync,keyword.status ";
		$sql .= " FROM campaign, adgroup, keyword ";
		$sql .= " where campaign.memberId = ".$_SESSION["memberId"]." ";
		$sql .= " AND campaign.camp_Id = adgroup.camp_Id ";
		$sql .= " AND keyword.adGr_Id = adgroup.adGr_Id ";
		$sql .= " AND keyword.negative = 1";
		if($this->camp != -1) $sql.= " and campaign.camp_Id =".$this->camp;
		if($this->media != -1) $sql.= " and campaign.listingType =".$this->media;
		if($this->keyName != "") $sql.= " and keyword.text like '%".$this->keyName."%'";
		if($this->show_deleted == 0) $sql.= " and keyword.status <> 9 ";
		echo '<!--'.$sql.'-->';
		$result = mysql_query($sql);
		$rows = mysql_num_rows($result);
		
		if ($rows > 0){
			$html.=	'<table class="tbl" width="100%" border="0" cellpadding="4" cellspacing="0">';
			$html.=	'<tbody><tr class="tr2" align="center"><td width="40" align="center">No</td><td width="12"></td>';
			$html.=	'<td align="left">キャンペーン</td><td align="left">広告グループ</td>';
			$html.=	'<td align="left">除外ワード</td></tr>';
			for ($i=0;$i<$rows;$i++)
			{
				$data = mysql_fetch_row($result);
				if($i%2 == "0"){
						$html .= ' <tr class="tr4">';
				}else{
					$html .= ' <tr class="tr3">';
				}
				$html.=	'<td align="center">'.($i+1).'</td>';
				if($data[4] == 9){
					$html.=	'<td align="center"><img src="../images/sync_deleted.gif" title="削除" border="0" /></td>';
				}else if($data[3] == 1){
					$html.=	'<td align="center"><img src="../images/sync_on.gif" title="同期済" border="0" /></td>';
				}else if($data[3] == 2){
					$html.=	'<td align="center"><img src="../images/sync_updating.gif" title="同期待ち" border="0" /></td>';
				}else if($data[3] == 3){
					$html.=	'<td align="center"><img src="../images/sync_error.gif" title="同期エラー" border="0" /></td>';
				}else {
					$html.=	'<td align="center"><img src="../images/sync_updating.gif" title="同期待ち" border="0" /></td>';
				}
				$html.=	'<td align="left">'.$data[0].'</td>';
	    		$html.=	'<td align="left">'.$data[1].'</td>';
	    		$html.=	'<td align="left">'.$data[2].'</td></tr>';
	  		}
			$html.=	'</tbody></table>';
			$html.=	'<table width="100%" border="0" cellpadding="2" cellspacing="0">';
			$html .= '<tr><td align="left"></td><td align="right"> 1件-'.$rows.'件/ '.$rows.'件&nbsp;&nbsp; </td></tr></table>';
		} else {
				$html .= "データはありません。";
		}
		echo $html;
		
	}
}
?>